﻿IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[esf_sso].[VLocatedBasicReference]'))
	DROP VIEW [esf_sso].[VLocatedBasicReference]
GO

-- SELECT * FROM [esf_sso].[VLocatedBasicReference] where Name = 'Bogota'

CREATE VIEW [esf_sso].[VLocatedBasicReference] AS
	SELECT 
		VBR.*
		
		,L.ID						Loc_ID
		,L.Name						Loc_Name
		,L.CityName					Loc_CityName
		,L.PhoneNumber				Loc_PhoneNumber
		,L.CellPhoneNumber			Loc_CellPhoneNumber
		,L.Address					Loc_Address
		,VC.ID						Loc_Cnt_ID
		,VC.Name					Loc_Cnt_Name
		,VCt.ID						Loc_City_ID
		,VCt.Name					Loc_City_Name
	FROM
		esf_sso.VBasicReference VBR
	LEFT JOIN
		esf_sso.BasicReference_Location BRL ON BRL.BasicReferenceID = VBR.ID
	LEFT JOIN
		esf_sso.Location L ON L.ID = BRL.LocationID
	LEFT JOIN
		esf_sso.VCountry VC ON VC.ID = L.CountryID
	LEFT JOIN
		esf_sso.VCity VCt ON VCt.ID = L.CityID
		
GO